package com.ld.hpfs.web.controller;

import com.google.common.collect.ImmutableMap;
import com.ld.hpfs.common.jackson.JackJson;
import com.ld.hpfs.common.utils.Const;
import com.ld.hpfs.common.utils.Tools;
import com.ld.hpfs.pojo.DbSourceOutting;
import com.ld.hpfs.pojo.ExtPager;
import com.ld.hpfs.pojo.ExtReturn;
import com.ld.hpfs.pojo.User;
import com.ld.hpfs.service.DbSourceOuttingService;
import com.ld.hpfs.service.ExportService;
import com.ld.hpfs.web.interseptor.WebConstants;
import org.apache.commons.io.IOUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.servlet.ModelAndView;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.io.PrintWriter;
import java.sql.DriverManager;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

@Controller
public class ExportController {

	private static final Logger logger = LoggerFactory
			.getLogger(DealController.class);
	public static HashMap<String, Object> releaseData = new HashMap<String, Object>();

	@Autowired
	private ExportService exportService;
	@Autowired
	private DbSourceOuttingService dbouttingService;
	private String TicketNum = "";

	/*
	 * getDealSearchList 获得发票已打印grid信息
	 */
	@RequestMapping(value = "/getExportWriteBackList")
	public void getExportWriteBackList(ExtPager page, HttpSession session,
			HttpServletRequest request, HttpServletResponse response,
			PrintWriter writer) {
		HashMap<String, Object> param = new HashMap<String, Object>();
		HashMap<String, Object> result = new HashMap<String, Object>();
		response.setContentType("text/plain;charset=UTF-8");
		HashMap<String, Object> orderBy = new HashMap<String, Object>();
		orderBy.put("id_head_id", "ID_HEAD_ID");
		// 页面查询结果一览条件
		String docStart = request.getParameter("docStart");
    	String docEnd = request.getParameter("docEnd");
    	String dateStart = request.getParameter("dateStart");
    	String dateEnd = request.getParameter("dateEnd");
    	String custCodeStart = request.getParameter("custCodeStart");
    	String custCodeEnd = request.getParameter("custCodeEnd");
    	String invType = request.getParameter("invType");
    	String invStatus = request.getParameter("invStatus");
    	// 票据类型初期值 增值税普通发票
    	if (Tools.isEmpty(invType)) {
    		invType = "N_VAT";
    	}
    	// 单据状态初期值 已开票
    	if (Tools.isEmpty(invStatus)) {
    		invStatus = "1";
    	}
    	String companyId = session.getAttribute(WebConstants.CURRENT_COMPANY).toString();
		try {
			User user = (User) session.getAttribute(WebConstants.CURRENT_USER);
			if (user != null) {
				String language = (String) session
						.getAttribute(WebConstants.CURRENT_LANG);
				param.put("language", language);
				param.put("pageEnd", page.getStart() + page.getLimit());
				param.put("companyId", companyId);
				param.put("orderBy", "ID_HEAD_ID");
				param.put("pageStart", page.getStart());
				param.put("user_id", user.getUserId());
				
				// 查询条件
				param.put("docStart", docStart);
				param.put("docEnd", docEnd);
				param.put("dateStart", dateStart);
				param.put("dateEnd", dateEnd);
				param.put("custCodeStart", custCodeStart);
				param.put("custCodeEnd", custCodeEnd);
				param.put("invStatus", invStatus);
				param.put("invType", invType);
				
				ArrayList<HashMap<String, Object>> logs = exportService
						.getWriteBackList(param);// 根据param参数查询数据库获得所需的结果集
				if (logs != null && logs.size() > 0) {
					result.put("success", true);
					result.put("total", param.get("pageCount"));
					result.put("data", logs);
				} else {
					result.put("success", true);
					result.put("total", param.get("pageCount"));
					result.put("data", "");
				}
				String returnMsg = JackJson.fromObjectToJson(result);
				logger.debug("{}", returnMsg);
				writer.print(returnMsg);
			} else {
				writer.print("");
			}
		} catch (Exception e) {
			logger.error("Exception: ", e);
		} finally {
			writer.flush();
			writer.close();
		}
	}

    /*
     * WriteBackDownload/SaveIds 保存需要回写的Id到session
     */
    @RequestMapping(value="/WriteBackDownload/SaveSelectedIds", method = RequestMethod.POST)
    @ResponseBody
    public Map<String, String> saveSelectedIds(@RequestParam("doc") String doc, HttpSession session) {
        session.setAttribute("doc", doc);
        return ImmutableMap.of("status", "success");
    }

	/*
	 * WriteBackDownload 导出回写TXT
	 */
	@RequestMapping(value = "/WriteBackDownload/{TicketType}/{ticketnum}/{TicketStatus}/download", method = RequestMethod.GET)
	public void WriteBackDownload(@PathVariable String TicketType, @PathVariable String ticketnum,
                                  @PathVariable String TicketStatus, HttpSession session, HttpServletResponse response) {
        String  Doc = (String) session.getAttribute("doc");
        //取传过来的原始单据号
        HashMap<String, Object> docname = new HashMap<String, Object>();
        String dd=null;
        dd=Doc.replace("("," ");
        dd=dd.replace(")", " ");
        docname.put("Doc", dd);
        docname.put("company_id", session.getAttribute(WebConstants.CURRENT_COMPANY).toString());

        ArrayList<HashMap<String, Object>> newdoc= exportService.getOrigiDocNo(docname);
        List docno=new ArrayList();
        String [] str=dd.split(",");
        for (String aStr : str) {
            if ((aStr.lastIndexOf("_S") < 0) && (aStr.lastIndexOf("_M") < 0)) {
                docno.add(aStr);
            }
        }
        String  newstr="";
        String newstr1="";
        for (int i = 0; i < docno.size(); i++) {
              newstr +=docno.get(i).toString()+",";
        }
        //两次循环看是否还存在拆分的票据，然后保存原始单据
        for (int i = 0; i < newdoc.size(); i++) {
            int begin=newdoc.get(i).get("origi_doc_no").toString().indexOf("_S");
            int begin1=newdoc.get(i).get("origi_doc_no").toString().indexOf("_M");
            if(begin>1 || begin1 >1){
                HashMap<String, Object> docordername = new HashMap<String, Object>();
                docordername.put("Doc", "'"+newdoc.get(i).get("origi_doc_no").toString()+"'");
                docordername.put("company_id", session.getAttribute(WebConstants.CURRENT_COMPANY));

                ArrayList<HashMap<String, Object>> orderdoc= exportService.getOrigiDocNo(docordername);
                for (int j = 0; j < orderdoc.size(); j++){
                    newstr1 +="'"+orderdoc.get(j).get("origi_doc_no").toString()+"'"+",";
                }
            }else{
             newstr1 +="'"+newdoc.get(i).get("origi_doc_no").toString()+"'"+",";
        }
        }
        if(newstr1 !="" && newstr1 !=null){
        int end=newstr1.lastIndexOf(",");
        newstr1=newstr1.substring(0,end);
        }else{
            int end=newstr.lastIndexOf(",");
            newstr=newstr.substring(0,end);
        }
        String newstr2="("+newstr+newstr1+")";
        TicketNum = ticketnum;
        // 从库中获取信息
        HashMap<String, Object> param = new HashMap<String, Object>();
        param.put("companyId", session.getAttribute(WebConstants.CURRENT_COMPANY).toString());
        // TICKET = TicketType;
        param.put("order", "ID_HEAD_ID");// 排序规则
        param.put("TicketStatus", TicketStatus);
        List<HashMap<String, String>> list = new ArrayList<HashMap<String, String>>();
        param.put("DOC", Doc == null ? "" : Doc);// 单据号
        param.put("NewDOC", newstr2 == null ? "" : newstr2);//回写原始单据号
        // for(int i=0;i<ids.length;i++){
        // map = new HashMap<String,String>();
        // map.put("id", ids[i]);
        // list.add(map);
        // }
        param.put("list", list);// 要导出的id_head_id

        ArrayList<HashMap<String, Object>> revenueData = exportService.getWriteBackForExport(param);
        StringBuffer write = new StringBuffer();
        String data = FormateDownloadData(revenueData);
        // 将数据转型为StringBuffer再将stringbuffer设置为UTF-8格式输出，解决乱码问题
        write.append(data);
        response.setCharacterEncoding("UTF-8");
        // 创建txt
        response.setHeader("Content-Disposition", "attachment; filename=\"download.txt\"");
        /* 创建输出流 */
			OutputStream servletOS;
        try {
            servletOS = response.getOutputStream();
            IOUtils.write(write.toString().getBytes("UTF-8"), servletOS);
            // 导出文件到服务器路径下
            // 获取项目所在路径
            String strPath = Const.REAL_PATH;
            // 获取当前时间
            Date date = new Date();
            java.text.DateFormat format = new java.text.SimpleDateFormat("yyyyMMddhhmmss");
            String time = format.format(date);
            if (strPath.contains("tomcat-7.0")) {
                String strfilePath = strPath.substring(0, strPath.indexOf("tomcat-7.0")) + "download//";
                File file = new File(strfilePath + "TXT-" + time + ".txt");

                OutputStreamWriter OutputStreamWriter = new OutputStreamWriter(new FileOutputStream(file),"utf-8");
                BufferedWriter writer=new BufferedWriter(OutputStreamWriter);
                writer.write(data);
                IOUtils.closeQuietly(writer);
            }
			servletOS.flush();
            IOUtils.closeQuietly(servletOS);
		} catch (Exception e) {
			logger.error("Exception: ", e);
		} finally {

		}
	}

	/*
	 * 导出回写----->导出excel[使用视图，读取值并写入EXCEL]
	 */
    @RequestMapping(value = "/WriteBackExportExcel/{TicketStatus}/download", method = RequestMethod.GET)
	public ModelAndView WriteBackExportExcel(@PathVariable String TicketStatus, HttpSession session) {
        String Doc_Head = (String) session.getAttribute("doc");
        HashMap<String, Object> param = new HashMap<String, Object>();
		int id = (Integer) session.getAttribute(WebConstants.CURRENT_COMPANY);
		param.put("companyId", id);
		param.put("DOC", Doc_Head);// 要导出的id_head_id
		param.put("TicketStatus", TicketStatus);// 要导出的票据状态：已开票、已红冲、已回写
		releaseData = param;

		List<HashMap<String, Object>> list = new ArrayList<HashMap<String, Object>>();
		// 在数据前加入标题列
		list.add(getCloumnsName());

        list.addAll(exportService.getExportExcelList(param));
		return new ModelAndView("WriteBackExcelView", "table", list);

	}

	/*
	 * 导出回写----->导出csv[使用视图，读取值并写入CSV]
	 */
    @RequestMapping(value = "/WriteBackExportCsv/{TicketStatus}/download", method = RequestMethod.GET)
	public void WriteBackExportCsv(@PathVariable String TicketStatus, HttpSession session, HttpServletResponse response) {
        String Doc_Head = (String) session.getAttribute("doc");
        HashMap<String, Object> param = new HashMap<String, Object>();
		int id = (Integer) session.getAttribute(WebConstants.CURRENT_COMPANY);
		param.put("companyId", id);
		param.put("DOC", Doc_Head);// 要导出的id_head_id
		param.put("TicketStatus", TicketStatus);// 要导出的票据状态：已开票、已红冲、已回写
		releaseData = param;

        List<HashMap<String, Object>> table = exportService.getExportExcelList(param);

		File csvFile = null;
		BufferedWriter csvFileOutputStream = null;

		try {
			// 获取项目所在路径
			String strPath = Const.REAL_PATH;
			Date date = new Date();
			java.text.DateFormat format = new java.text.SimpleDateFormat(
					"yyyyMMddhhmmss");
			String time = format.format(date);
			if (strPath.contains("tomcat-7.0")) {
				String strfilePath = strPath.substring(0,
						strPath.indexOf("tomcat-7.0"))
						+ "download//";
				csvFile = new File(strfilePath + "CSV-" + time + ".csv");
				File parent = csvFile.getParentFile();
				if (parent != null && !parent.exists()) {
					parent.mkdirs();
				}
				csvFile.createNewFile();
			}
			ArrayList<String> titles = new ArrayList<String>();
			// 设置csv表头内容
			HashMap<String, Object> titleNameMap=this.getCloumnsName();
			ArrayList<HashMap<String, Object>> titleName =(ArrayList<HashMap<String, Object>>)titleNameMap.get("columnnames");
			for (int i = 0; i < titleName.size(); i++) {
				titles.add(titleName.get(i).get("name").toString());
			}
			// GB2312使正确读取分隔符","
			csvFileOutputStream = new BufferedWriter(new OutputStreamWriter(
					new FileOutputStream(csvFile), "GB2312"), 1024);
			// 写入文件头部
			for (int i = 0; i < titles.size(); i++) {
				csvFileOutputStream.write("\"" + titles.get(i).toString() + "\"");
				csvFileOutputStream.write(",");
			}
			csvFileOutputStream.newLine();

			// 写入文件内容

			for (Iterator iterator = table.iterator(); iterator.hasNext();) {

				for (HashMap<String, Object> entry : table) {
					for (int i = 0; i < titles.size(); i++) {
						String tmp=entry.get(titles.get(i)) == null ? ""
								: entry.get(titles.get(i)).toString();
						csvFileOutputStream.write(tmp+",");
					}
					
					if (iterator.hasNext()) {
						csvFileOutputStream.newLine();
					}
					iterator.next();
				}
			}
			csvFileOutputStream.flush();

			response.addHeader(
					"Content-Disposition",
					"attachment; filename=\""
							+ new String("download.csv".getBytes("gb2312"),
									"ISO8859-1") + "\"");
			byte[] b = new byte[100];
			int len;
			try {
				// 读到流中
				FileInputStream inStream = new FileInputStream(csvFile);// 文件的存放路径
				while ((len = inStream.read(b)) > 0)
					response.getOutputStream().write(b, 0, len);
				inStream.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				csvFileOutputStream.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}

	private String FormateDownloadData(
			ArrayList<HashMap<String, Object>> revenueData) {// 拼写txt文件内容
		try {
			Date date = new Date();
			SimpleDateFormat from = new SimpleDateFormat("yyyyMMdd"); // 获取服务器时间并格式化，格式为"yyyy-MM-dd"
			String SYSTIME = from.format(date); // SYSTEM TIME

			StringBuffer sb = new StringBuffer();
			sb.append("SJJK0201~~销售单据传出");
			sb.append("\r\n");
			sb.append(TicketNum).append("~~").append(SYSTIME).append("~~")
					.append(SYSTIME);
			sb.append("\r\n");
			if (!revenueData.isEmpty()) {
				// sb.append(
				// "DOCUMENT_NO~~").append("COUNT~~").append("CUST_NAME_CHN~~").append("TAX_CODE~~").append("ADDRESS~~").append("BANK_ACCOUNT~~")
				// .append("REMARK~~").append("CHECKER~~").append("REMITTEE~~")
				// .append("GOODS_LIST_NAME~~").append("DOCUMENT_DATE~~").append("SELLER_BANK_ACCOUNT~~")
				// .append("SELLER_ADDRESS~~");
				// sb.append("\r\n");

				for (int i = 0; i < revenueData.size(); i++) {
					ArrayList<HashMap<String, Object>> details_other = (ArrayList<HashMap<String, Object>>) revenueData
							.get(i).get("details");
					String tax_rate_main = "";
					try {
						tax_rate_main = details_other.get(0).get("TAX_RATE") == null ? ""
								: details_other.get(0).get("TAX_RATE")
										.toString();
					} catch (Exception ex33) {
						tax_rate_main = "";
					}
					String document_no = revenueData.get(i).get("DOCUMENT_NO")
							.toString();
					// String doc_type_code =
					// revenueData.get(i).get("DOC_TYPE_CODE").toString();
					String cust_name_chn = revenueData.get(i)
							.get("CUST_NAME_CHN").toString();
					String total_line_no = revenueData.get(i)
							.get("TOTAL_LINE_NO").toString();
					String tax_code = revenueData.get(i).get("TAX_CODE")
							.toString();
					String doc_type_code = revenueData.get(i).get(
							"DOC_TYPE_CODE") == null ? "" : revenueData.get(i)
							.get("DOC_TYPE_CODE").toString();
					// String doc_type = revenueData.get(i).get("DOCUMENT_TYPE")
					// == null ? "" :
					// revenueData.get(i).get("DOCUMENT_TYPE").toString();
					String address = revenueData.get(i).get("ADDRESS")
							.toString();
					String bank_account = revenueData.get(i)
							.get("BANK_ACCOUNT").toString();
					String untax_money = revenueData.get(i).get(
							"AMOUNT_CUR_TOTAL") == null ? "" : revenueData
							.get(i).get("AMOUNT_CUR_TOTAL").toString();
					String void_flag = revenueData.get(i).get("VOID_FLAG") == null ? ""
							: revenueData.get(i).get("VOID_FLAG").toString();
					String main_tax_amount = revenueData.get(i).get(
							"TAX_AMOUNT") == null ? "" : revenueData.get(i)
							.get("TAX_AMOUNT").toString();
					String remark = revenueData.get(i).get("REMARK") == null ? ""
							: revenueData.get(i).get("REMARK").toString();
					String checker = revenueData.get(i).get("CHECKER")
							.toString();
					String remittee = revenueData.get(i).get("REMITTEE")
							.toString();
					// String goods_list_name =
					// revenueData.get(i).get("GOODS_LIST_NAME").toString();
					String document_date = revenueData.get(i)
							.get("INVOICE_DATE").toString();
					String seller_bank_account = revenueData.get(i)
							.get("SELLER_BANK_ACCOUNT").toString();
					String invoice_no = revenueData.get(i).get("INVOICE_NO") == null ? ""
							: revenueData.get(i).get("INVOICE_NO").toString();
					String invoice_code = revenueData.get(i).get("INVOICE_CODE") == null ? ""
							: revenueData.get(i).get("INVOICE_CODE").toString();
					String month = document_date.length() < 1 ? ""
							: document_date.substring(4, 6);
					String seller_tax_code = revenueData.get(i)
							.get("SELLER_TAX_CODE").toString();
					String list_mark = Integer.parseInt(total_line_no) > 8 ? "1"
							: "0";
					String seller_name = revenueData.get(i).get("SELLER_NAME")
							.toString();
					String seller_address = revenueData.get(i)
							.get("SELLER_ADDRESS").toString();
					String operator = revenueData.get(i).get("OPERATOR")
							.toString();
					String count = revenueData.get(i).get("count").toString();
					int countNum=i+1;
					sb.append("//"  + "发票"+countNum);
					sb.append("\r\n");
					sb.append(void_flag)
							.append("~~")
							.append(list_mark)
							.append("~~")
							/* .append(doc_type).append("~~") */.append(
									doc_type_code.equals("VAT") ? "0" : "1")
							.append("~~")
							.append(invoice_code)
							.append("~~")
							.append(invoice_no)
							.append("~~")
							.append(total_line_no == null ? "" : total_line_no)
							.append("~~")
							.append(document_date == "" ? "" : document_date)
							.append("~~")
							.append(month)
							.append("~~")
							.append(document_no == null ? "" : document_no)
							.append("~~")
							.append(untax_money == null ? "" : untax_money)
							.append("~~")
							.append(tax_rate_main)
							.append("~~")
							.append(main_tax_amount == "" ? "~~"
									: main_tax_amount)
							.append("~~")
							.append(cust_name_chn == null ? "" : cust_name_chn)
							.append("~~")
							.append(tax_code == null ? "" : tax_code)
							.append("~~")
							.append(address == null ? "" : address)
							.append("~~")
							.append(bank_account == null ? "" : bank_account)
							.append("~~")
							.append(seller_name == null ? "" : seller_name)
							.append("~~")
							.append(seller_tax_code == null ? ""
									: seller_tax_code)
							.append("~~")
							.append(seller_address == null ? ""
									: seller_address)
							.append("~~")
							.append(seller_bank_account == null ? ""
									: seller_bank_account).append("~~")
							.append(remark).append("~~")
							.append(operator == null ? "" : operator)
							.append("~~")
							.append(checker == null ? "" : checker)
							.append("~~")
							.append(remittee == null ? "" : remittee);
					sb.append("\r\n");
					if (Integer.parseInt(count) > 0) {
						ArrayList<HashMap<String, Object>> details = (ArrayList<HashMap<String, Object>>) revenueData
								.get(i).get("details");
						for (int j = 0; j < details.size(); j++) {
							String goods_name = details.get(j)
									.get("GOODS_NAME").toString();
							String goods_unit = details.get(j)
									.get("GOODS_UNIT").toString();
							String spec = details.get(j).get("SPEC").toString();
							String discount_line = "0";
							String goods_quantityname_chn = details.get(j).get(
									"QUANTITY") == null ? "" : details.get(j)
									.get("QUANTITY").toString();
							String amount_cur = details.get(j)
									.get("AMOUNT_CUR") == null ? "" : details
									.get(j).get("AMOUNT_CUR").toString();
							String tax_rate = details.get(j).get("TAX_RATE") == null ? ""
									: details.get(j).get("TAX_RATE").toString();
							String tax_item = details.get(j).get("TAX_ITEM")
									.toString();
							// String discount_sum =
							// details.get(j).get("DISCOUNT_SUM").toString();
							String tax_amount = details.get(j)
									.get("TAX_AMOUNT") == null ? "" : details
									.get(j).get("TAX_AMOUNT").toString();
							// String discount_tax =
							// details.get(j).get("DISCOUNT_TAX").toString();
							// String discount_rate =
							// details.get(j).get("DISCOUNT_RATE").toString();
							String price_cur = details.get(j).get("PRICE_CUR") == null ? ""
									: details.get(j).get("PRICE_CUR")
											.toString();
							String price_way = details.get(j).get("PRICE_WAY")
									.toString();
							sb.append(discount_line)
									.append(goods_name == null ? ""
											: goods_name)
									.append("~~")
									.append(spec == null ? "" : spec)
									.append("~~")
									.append(goods_unit == null ? ""
											: goods_unit).append("~~")
									.append(goods_quantityname_chn)
									.append("~~").append(amount_cur)
									.append("~~")
									.append(tax_rate == null ? "" : tax_rate)
									.append("~~").append(tax_amount)
									.append("~~")
									.append(price_cur == null ? "" : price_cur)
									.append("~~").append(price_way)
									.append("~~")
									.append(tax_item == null ? "" : tax_item);
							sb.append("\r\n");
						}
					}
				}
			}
			try {
				return sb.toString();
			} catch (Exception e) {
				e.printStackTrace();
			}
		} catch (Exception e2) {
			e2.printStackTrace();
		}

		return "";
	}

	/* 导出回写ERP[使用视图，select视图字段到SQL语句中并替换"{}"值] */
	@RequestMapping(value = "/writebackexporterp")
	@ResponseBody
	public Object writebackexporterp(@RequestParam String ids,
			@RequestParam String TicketStatus, HttpSession session,
			HttpServletRequest request) {

		HashMap<String, Object> param = new HashMap<String, Object>();
		int id = (Integer) session.getAttribute(WebConstants.CURRENT_COMPANY);
		param.put("companyId", id);

		int CorrectId = exportService.getCorrectId(param); // 新增：验证当前公司是否有引用的导出数据源
															// 2013.4.2
		if (CorrectId != id) {
			return new ExtReturn(false, "当前公司无引用的导出数据源");
		}
		// List<HashMap<String,String>> lists = new
		// ArrayList<HashMap<String,String>>();
		// String[] Ids = ids.split(",");
		// HashMap<String,String> map= new HashMap<String,String>();
		// for(int i=0;i<Ids.length;i++){
		// map = new HashMap<String,String>();
		// map.put("id", Ids[i]);
		// lists.add(map);
		// }
		param.put("DOC", ids);// 要导出的id_head_id
		param.put("TicketStatus", TicketStatus);// 要导出的票据状态：已开票、已红冲、已回写
		// param.put("list", lists);//要导出的id_head_id
		List<HashMap<String, Object>> list = exportService
				.getExportExcelList(param); // 导出回写勾选的单据，会导出视图的字段名

		int ERP_ID = dbouttingService.getDbOuttingERPID(id); // new
																// 2013.4.28：先获取当前公司的ACK_ERP_ID号，然后根据此号找出ERP数据源

		String message = "运行成功";

		for (int i = 0; i < list.size(); i++) {

			DbSourceOutting outting = dbouttingService
					.getDbSourceOutting(ERP_ID);

			String sql_sources = "";

			String driver = null;
			java.sql.Connection dbConn = null;
			Statement sta = null;

			if (outting.getDs_type().equals("oracle")) {
				driver = "oracle.jdbc.driver.OracleDriver";
			} else // sqlserver
			{
				driver = "net.sourceforge.jtds.jdbc.Driver";
			}

			String connectString = outting.getDs_connect_str();

			try {

				Class.forName(driver);

				dbConn = DriverManager.getConnection(connectString,
						outting.getDs_username(), outting.getDs_password());

				sta = dbConn.createStatement();

				HashMap<String, Object> beanMap = list.get(i); // 视图获取的字段名
				sql_sources = outting.getSql_str(); // 原始SQL语句
				String[] sql_groups = sql_sources.split(";");// 若有多条SQL语句，则使用";"隔开每条SQL语句并进行依次执行到数据库
				if (sql_groups.length < 1) { // 如果split()失败，则默认将源SQL语句执行。
					sql_groups[0] = sql_sources;
				}
				for (int gi = 0; gi < sql_groups.length; gi++) {
					String sql = sql_groups[gi];// 每条SQL语句
					int pos = 0; // 定位"{}"
					int cnt2 = 0; // 定位"{}"
					while (pos < sql.length()) { // 替换"{字段名}"模块
						pos = sql.indexOf("{", pos);
						cnt2 = sql.indexOf("}", pos);
						if (pos == -1) {
							break;
						} else {
							String sub2 = sql.substring(pos + 1, cnt2); // 被"{}"括起，需要替换值的字段，格式为：字段名

							cnt2++;
							String sub = sql.substring(pos, cnt2); // 包含在"{}"的需要替换的字段，格式为：{字段名}

							if (beanMap.get(sub2) == null) { // 与后台获取的单据比较，若不存在字段，则退出，
								// 比较的字段名即：<视图字段名(beanMap)> -- <用户配置的字段名(sub2)>
								message = "字段{" + sub2 + "}不存在";
								exportService.ReleaseDealData(param); // 导出回写错误，撤销修改
								return new ExtReturn(false, message);
							}

							sql = sql.replace(sub, "'"
									+ beanMap.get(sub2).toString() + "'"); // 替换后的完整字段
							System.out.println(sql);
							// sta.execute(sql);
						}
					}
					sta.execute(sql); // 执行该SQL语句
					// ResultSet resultSet = sta.executeQuery(sql); //执行该SQL语句
					// while(resultSet.next())
					// {
					// String resultStr = resultSet.getString(1);
					// System.out.println(resultStr);
					// }
				}

			} catch (Exception e) {
				exportService.ReleaseDealData(param); // 导出回写错误，撤销修改
				e.printStackTrace();
				message = e.getMessage();
			} finally {
				try {
					if (sta != null) {
						sta.close();
					}

					if (dbConn != null) {
						dbConn.close();
					}
				} catch (Exception ex) {

				}
			}

		}

		if ("运行成功".equals(message)) {
			return new ExtReturn(true, message);
		} else {
			return new ExtReturn(false, "运行失败：" + message);
		}
	}

	/* 获得当前用户导出回写的计划任务 */
	@RequestMapping(value = "/getwritebackplaninfo")
	public void getwritebackplaninfo(ExtPager page, HttpSession session,
			HttpServletRequest request, HttpServletResponse response,
			PrintWriter writer) {
		HashMap<String, Object> param = new HashMap<String, Object>();
		HashMap<String, Object> result = new HashMap<String, Object>();
		response.setContentType("text/plain;charset=UTF-8");
		try {
			User user = (User) session.getAttribute(WebConstants.CURRENT_USER);
			if (user != null) {
				param.put("companyId",
						session.getAttribute(WebConstants.CURRENT_COMPANY)
								.toString());

				result = exportService.getwritebackplaninfo(param);
				if (result == null) {
					writer.print("3");
				} else {
					String returnMsg = JackJson.fromObjectToJson(result);
					writer.print(returnMsg);
				}

			} else {
				writer.print("2");
			}
		} catch (Exception e) {
			logger.error("Exception: ", e);
			writer.print("1");
		} finally {
			writer.flush();
			writer.close();

		}
	}

	/* 保存当前用户导出回写的计划任务 */
	@RequestMapping(value = "/savewritebackplaninfo")
	public void savewritebackplaninfo(ExtPager page, HttpSession session,
			HttpServletRequest request, HttpServletResponse response,
			PrintWriter writer) {
		HashMap<String, Object> param = new HashMap<String, Object>();
		HashMap<String, Object> result = new HashMap<String, Object>();
		response.setContentType("text/plain;charset=UTF-8");
		try {
			User user = (User) session.getAttribute(WebConstants.CURRENT_USER);
			if (user != null) {
				String startDay = request.getParameter("startDay") == null ? ""
						: (String) request.getParameter("startDay");
				String startTime = request.getParameter("startTime") == null ? ""
						: (String) request.getParameter("startTime");
				String endDay = request.getParameter("endDay") == null ? ""
						: (String) request.getParameter("endDay");
				String endTime = request.getParameter("endTime") == null ? ""
						: (String) request.getParameter("endTime");
				String LOOP_TYPE = request.getParameter("LOOP_TYPE") == null ? ""
						: (String) request.getParameter("LOOP_TYPE");
				startDay = startDay.substring(0, 10);
				startTime = startTime.substring(startTime.length() - 8);
				endDay = endDay.substring(0, 10);
				endTime = endTime.substring(endTime.length() - 8);
				String DT = startDay + " " + startTime;
				String ET = endDay + " " + endTime;
				String now = Tools.date2Str(new Date());// 获得当前日期
				param.put("DT", DT);
				param.put("ET", ET);
				param.put("LOOP_TYPE", Integer.parseInt(LOOP_TYPE));
				param.put("user", user.getAccount());
				param.put("now", now);
				param.put("companyId",
						session.getAttribute(WebConstants.CURRENT_COMPANY)
								.toString());
				result = exportService.getwritebackplaninfo(param);
				if (result == null || result.isEmpty()) {// 不存在的话就保存这条回写任务信息
					exportService.saveplaninfo(param);
				} else {// 如果存在当前公司的回写任务信息，就更新
					exportService.updateplaninfo(param);
				}

				writer.print("3");
			} else {
				writer.print("2");
			}
		} catch (Exception e) {
			logger.error("Exception: ", e);
			writer.print("1");
		} finally {
			writer.flush();
			writer.close();

		}
	}

	/* 显示当前用户导出回写的计划任务详情 */
	@RequestMapping(value = "/getExportWriteBackTask")
	public void getExportWriteBackTask(ExtPager page, HttpSession session,
			HttpServletRequest request, HttpServletResponse response,
			PrintWriter writer) {
		HashMap<String, Object> param = new HashMap<String, Object>();
		HashMap<String, Object> result = new HashMap<String, Object>();
		response.setContentType("text/plain;charset=UTF-8");
		try {
			User user = (User) session.getAttribute(WebConstants.CURRENT_USER);
			if (user != null) {
				param.put("pageStart", page.getStart());
				param.put("pageEnd", page.getStart() + page.getLimit());
				param.put("companyId",
						session.getAttribute(WebConstants.CURRENT_COMPANY)
								.toString());
				List<HashMap<String, Object>> resultList = exportService
						.getExportWriteBackTask(param);
				if (resultList != null && resultList.size() > 0) {
					result.put("success", true);
					result.put("total", param.get("pageCount"));
					result.put("data", resultList);
				} else {
					result.put("success", true);
					result.put("total", param.get("pageCount"));
					result.put("data", "");
				}
				String returnMsg = JackJson.fromObjectToJson(result);
				logger.debug("{}", returnMsg);
				writer.print(returnMsg);
			} else {
				writer.print("2");
			}
		} catch (Exception e) {
			logger.error("Exception: ", e);
			writer.print("1");
		} finally {
			writer.flush();
			writer.close();

		}

	}

	private HashMap<String, Object> getCloumnsName() {
		// 导出视图名称
		HashMap<String, Object> viewName = new HashMap<String, Object>();
		viewName.put("viewName", "tax_v_id_head_ack");
		List<HashMap<String, Object>> columnnames = exportService
				.getExportColumnNames(viewName);
		HashMap<String, Object> map = new HashMap<String, Object>();
		map.put("columnnames", columnnames);
		return map;
	}
}
